﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[fnMSX_get_next_msx_group]
#-- Purpose:		Using the built in target server groups, find which group
#--					has the least targets assigned to it
#--	Last Update:	07/13/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE FUNCTION [dbo].[fnMSX_get_next_msx_group]
(
	@server_name			varchar(256)
)
RETURNS SYSNAME
AS
  BEGIN
	--- Declare Local Variables
	DECLARE	@retVal sysname

	--- Determine the next MSX Group to assign
	SELECT		@retVal = name
	FROM		(
				SELECT		TOP 1 sg.name
				FROM		[msdb].[dbo].[systargetservergroups] sg
				LEFT JOIN	[msdb].[dbo].[systargetservergroupmembers] mbr ON sg.servergroup_id = mbr.servergroup_id
				LEFT JOIN	[msdb].[dbo].[systargetservers] srv ON srv.server_id = mbr.server_id
				WHERE		REPLACE(sg.name, 'MSX ', '') BETWEEN 1 AND 5
							AND (
								srv.[server_name] IN (
									SELECT		t2.[ssd_sqlserver_name_ro]
									FROM		[dbo].[tblMSX_server_discovery] t1
									JOIN		[dbo].[tblMSX_server_discovery] t2 ON t1.[ssd_cluster_name] = t2.[ssd_cluster_name]
									WHERE		t1.[ssd_sqlserver_name_ro] = @server_name
								)
								OR NOT EXISTS (
									SELECT		t2.[ssd_sqlserver_name_ro]
									FROM		[dbo].[tblMSX_server_discovery] t1
									JOIN		[dbo].[tblMSX_server_discovery] t2 ON t1.[ssd_cluster_name] = t2.[ssd_cluster_name]
									WHERE		t1.[ssd_sqlserver_name_ro] = @server_name
								)
							)
				GROUP BY	sg.name
				ORDER BY	SUM(CASE WHEN mbr.servergroup_id IS NULL THEN 0 ELSE 1 END), 
							sg.name
				) t1
	
	IF @retVal IS NULL
		SELECT		@retVal = name
		FROM		(
					SELECT		TOP 1 srv.name
					FROM		[msdb].[dbo].[systargetservergroups] srv
					LEFT JOIN	[msdb].[dbo].[systargetservergroupmembers] mbr ON srv.servergroup_id = mbr.servergroup_id
					WHERE		REPLACE(srv.name, 'MSX ', '') BETWEEN 1 AND 5
					GROUP BY	srv.name
					ORDER BY	SUM(CASE WHEN mbr.servergroup_id IS NULL THEN 0 ELSE 1 END), 
								srv.name
					) t1

	--- Return the value
	RETURN @retVal
  END